package com.ctshk.rpc.order.tour.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.order.tour.code.*;
import com.ctshk.rpc.order.tour.req.SeriesOrderDetailReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderListReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderListSpanReq;
import com.ctshk.rpc.order.tour.req.SeriesOrderMyGroupIdQueryReq;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Objects;

/**
 * <p>
 * 系列团订单 Provider 接口
 * </p>
 *
 * @author 谭响
 * @since 2021-01-18
 */
public class SeriesOrderProvider {

    public String selectCountBySelective(SeriesOrderListReq req) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append("tso.id, tso.schedule_id, sum(tso.total_people) as total_people, sum(tsoas.total_receivables) as total_receivables, " +
                "sum(tsoas.total_paid_in) as total_paid_in, sum(tsoas.remaining_receivables) as remaining_receivables ");
        sb.append("from t_series_order tso ");
        sb.append("left join t_series_order_amount_statistics tsoas ON tso.id = tsoas.order_id ");
        sb.append("left join ( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_refund temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotr ON tsotr.order_id = tso.id ");
        sb.append("left join ( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_quit temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotq ON tsotq.order_id = tso.id ");
        sb.append("where 1=1 ");
        if (StringUtils.isNotBlank(req.getOrderNumber())) {
            sb.append(" and tso.order_number=#{req.orderNumber}");
        }
        if (StringUtils.isNotBlank(req.getCondition1())) {
            sb.append(" and tso.id IN (SELECT tsoti.order_id FROM t_series_order_tourist_info tsoti WHERE " +
                    "((tsoti.cn_surname || tsoti.cn_name) LIKE concat('%', #{req.condition1}, '%') " +
                    "OR (tsoti.en_surname || tsoti.en_name) LIKE concat('%', #{req.condition1}, '%') " +
                    "OR (tsoti.mobile LIKE concat('%',  #{req.condition1}, '%') " +
                    "OR (tsoti.email LIKE concat('%',  #{req.condition1}, '%'))))) ");
        }
        if (StringUtils.isNotBlank(req.getCondition2())) {
            sb.append(" and ((tso.schedule_number LIKE concat('%', #{req.condition2}, '%') " +
                    "OR (tso.trip_title LIKE concat('%', #{req.condition2}, '%') ");
        }
        if (req.getPayStatus() != null) {
            if (Objects.equals(req.getPayStatus(), PayStatusCode.FULL_PAYED.getCode())) {
                sb.append(" and tsoas.total_paid_in >= tsoas.total_receivables ");
            } else if (Objects.equals(req.getPayStatus(), PayStatusCode.WAIT_REMAIN.getCode())) {
                sb.append(" and tsoas.total_paid_in > 0 and tsoas.total_paid_in < tsoas.total_receivables ");
            } else if (Objects.equals(req.getPayStatus(), PayStatusCode.WAIT.getCode())) {
                sb.append(" and tsoas.total_paid_in = 0 ");
            }
        }
        if (req.getStartTime() != null) {
            sb.append(" and tso.gmt_create >= #{req.startTime} ");
        }
        if (req.getEndTime() != null) {
            sb.append(" and tso.gmt_create <= #{req.endTime} ");
        }
        if (req.getOrderSource() != null) {
            sb.append(" and tso.order_source = #{req.orderSource} ");
        }
        if (req.getScheduleStatus() != null) {
            sb.append(" and tso.schedule_status = #{req.scheduleStatus} ");
        }
        if (Objects.equals(req.getIsMy(), ISCode.YES.getCode())) {
            sb.append(" and tso.create_id = #{req.myId} ");
        }
        if (req.getCreateId() != null) {
            sb.append(" and tso.create_id = #{req.createId} ");
        }
        if (req.getRefundStatus() != null) {
            sb.append(" and tsotr.refund_status = #{req.refundStatus} ");
        }
        if (req.getQuitRefundStatus() != null) {
            sb.append(" and tsotq.refund_status = #{req.quitRefundStatus} ");
        }
        if (Objects.equals(req.getIsKeep(), ISCode.YES.getCode())) {
            sb.append(" and tso.plus_tourist_type = " + PlusTouristTypeCode.KEEP.getCode() + " ");
            sb.append(" and tsoas.total_paid_in = 0 ");
        }
        if (Objects.equals(req.getIsAdd(), ISCode.YES.getCode())) {
            sb.append(" and tso.plus_tourist_type = " + PlusTouristTypeCode.ADD.getCode() + " ");
            sb.append(" and tso.place_order_status = " + PlaceOrderStatusCode.APPROVAL.getCode() + " ");
        }
        sb.append(" and tso.is_deleted=" + IsDeletedCode.NO.getCode() + " ");
        sb.append(" and tso.place_order_status in ( " + PlaceOrderStatusCode.CONFIRM.getCode()
                + "," + PlaceOrderStatusCode.APPROVAL.getCode()
                + "," + PlaceOrderStatusCode.APPROVAL_PASS.getCode() +" ) ");
        List<Long> authUserIdList = req.getAuthUserIdList();
        int size = authUserIdList.size();
        if (size > 0) {
            sb.append(" and tso.create_id in ( ");
            StringBuilder temp = new StringBuilder();
            int i = 0;
            for (Long id : authUserIdList) {
                i++;
                temp.append("#{req.authUserIdList[" + (i - 1) + "]}");
                if (i < size) {
                    temp.append(",");
                }
            }
            sb.append(temp);
            sb.append(" ) ");
        }
        return sb.toString();
    }

    public String selectListBySelective(IPage<SeriesOrderListReq> page, SeriesOrderListReq req) {
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append("tso.id, tso.schedule_id, tso.create_id, tso.gmt_create, tso.order_number, tso.plus_tourist_type,tso.place_order_status,tso.salesman_id, " +
                "(SELECT group_concat(CONCAT(temp_tsoti.tourist_type_name,'])',temp_tsoti.people) SEPARATOR '})') " +
                "FROM(SELECT tsoti.main_data_id, tsoti.tourist_type_name, count(*) AS people FROM t_series_order_tourist_info tsoti " +
                "WHERE tsoti.order_id = tso.id GROUP BY tsoti.main_data_id ) temp_tsoti) as touristPeopleList, " +
                "tsoas.total_receivables, tsoas.total_paid_in, tsoas.remaining_receivables, tsotr.refund_status, tsotq.refund_status AS quit_refund_status, " +
                "tso.trip_id, tso.trip_title, tso.schedule_number, tso.depart_date, tso.schedule_status, tso.order_source ");
        sb.append("from t_series_order tso ");
        sb.append("left join t_series_order_amount_statistics tsoas ON tso.id = tsoas.order_id ");
        sb.append("left join ( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_refund temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotr ON tsotr.order_id = tso.id ");
        sb.append("left join ( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_quit temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotq ON tsotq.order_id = tso.id ");
        sb.append("where 1=1 ");
        if (StringUtils.isNotBlank(req.getOrderNumber())) {
            sb.append(" and tso.order_number=#{req.orderNumber}");
        }
        if (StringUtils.isNotBlank(req.getCondition1())) {
            sb.append(" and tso.id IN (SELECT tsoti.order_id FROM t_series_order_tourist_info tsoti WHERE " +
                    "((tsoti.cn_surname || tsoti.cn_name) LIKE concat('%', #{req.condition1}, '%') " +
                    "OR (tsoti.en_surname || tsoti.en_name) LIKE concat('%', #{req.condition1}, '%') " +
                    "OR (tsoti.mobile LIKE concat('%',  #{req.condition1}, '%') " +
                    "OR (tsoti.email LIKE concat('%',  #{req.condition1}, '%'))))) ");
        }
        if (StringUtils.isNotBlank(req.getCondition2())) {
            sb.append(" and ((tso.schedule_number LIKE concat('%',  #{req.condition2}, '%') " +
                    "OR (tso.trip_title LIKE concat('%',  #{req.condition2}, '%') ");
        }
        if (req.getPayStatus() != null) {
            if (Objects.equals(req.getPayStatus(), PayStatusCode.FULL_PAYED.getCode())) {
                sb.append(" and tsoas.total_paid_in >= tsoas.total_receivables ");
            } else if (Objects.equals(req.getPayStatus(), PayStatusCode.WAIT_REMAIN.getCode())) {
                sb.append(" and tsoas.total_paid_in > 0 and tsoas.total_paid_in < tsoas.total_receivables ");
            } else if (Objects.equals(req.getPayStatus(), PayStatusCode.WAIT.getCode())) {
                sb.append(" and tsoas.total_paid_in = 0 ");
            }
        }
        if (req.getStartTime() != null) {
            sb.append(" and tso.gmt_create >= #{req.startTime} ");
        }
        if (req.getEndTime() != null) {
            sb.append(" and tso.gmt_create <= #{req.endTime} ");
        }
        if (req.getOrderSource() != null) {
            sb.append(" and tso.order_source = #{req.orderSource} ");
        }
        if (req.getScheduleStatus() != null) {
            sb.append(" and tso.schedule_status = #{req.scheduleStatus} ");
        }
        if (Objects.equals(req.getIsMy(), ISCode.YES.getCode())) {
            sb.append(" and tso.create_id = #{req.myId} ");
        }
        if (req.getCreateId() != null) {
            sb.append(" and tso.create_id = #{req.createId} ");
        }
        if (req.getRefundStatus() != null) {
            sb.append(" and tsotr.refund_status = #{req.refundStatus} ");
        }
        if (req.getQuitRefundStatus() != null) {
            sb.append(" and tsotq.refund_status = #{req.quitRefundStatus} ");
        }
        if (Objects.equals(req.getIsKeep(), ISCode.YES.getCode())) {
            sb.append(" and tso.plus_tourist_type = " + PlusTouristTypeCode.KEEP.getCode() + " ");
            sb.append(" and tsoas.total_paid_in = 0 ");
        }
        if (Objects.equals(req.getIsAdd(), ISCode.YES.getCode())) {
            sb.append(" and tso.plus_tourist_type = " + PlusTouristTypeCode.ADD.getCode() + " ");
            sb.append(" and tso.place_order_status = " + PlaceOrderStatusCode.APPROVAL.getCode() + " ");
        }
        sb.append(" and tso.is_deleted=" + IsDeletedCode.NO.getCode() + " ");
    //    sb.append(" and tso.place_order_status=" + PlaceOrderStatusCode.CONFIRM.getCode() + " ");
        List<Long> authUserIdList = req.getAuthUserIdList();
        int size = authUserIdList.size();
        if (size > 0) {
            sb.append(" and tso.salesman_id in ( ");
            StringBuilder temp = new StringBuilder();
            int i = 0;
            for (Long id : authUserIdList) {
                i++;
                temp.append("#{req.authUserIdList[" + (i - 1) + "]}");
                if (i < size) {
                    temp.append(",");
                }
            }
            sb.append(temp);
            sb.append(" ) ");

            /*sb.append(" or tso.salesman_id in ( ");
            StringBuilder temp2 = new StringBuilder();
            int i2 = 0;
            for (Long id : authUserIdList) {
                i++;
                temp.append("#{req.authUserIdList[" + (i2 - 1) + "]}");
                if (i < size) {
                    temp2.append(",");
                }
            }
            sb.append(temp2);
            sb.append(" ) ");*/
        }
        sb.append(" order by tso.gmt_create desc ");
        return sb.toString();
    }

    public String selectListSpanBySelective(IPage<SeriesOrderListSpanReq> page, @Param("req") SeriesOrderListSpanReq req) {
        return new SQL(){{
            SELECT("tso.id, tso.schedule_id, tso.create_id, tso.gmt_create, tso.order_number, tso.plus_tourist_type, " +
                    "(SELECT group_concat(CONCAT(temp_tsoti.tourist_type_name,'])',temp_tsoti.people) SEPARATOR '})') " +
                    "FROM(SELECT tsoti.main_data_id, tsoti.tourist_type_name, count(*) AS people FROM t_series_order_tourist_info tsoti " +
                    "WHERE tsoti.order_id = tso.id GROUP BY tsoti.main_data_id ) temp_tsoti) as touristPeopleList, " +
                    "tsoas.total_receivables, tsoas.total_paid_in, tsoas.remaining_receivables, tsotr.refund_status, tsotq.refund_status AS quit_refund_status, " +
                    "tso.trip_id, tso.trip_title, tso.schedule_number, tso.depart_date, tso.schedule_status, tso.order_source ");
            FROM("t_series_order tso");
            LEFT_OUTER_JOIN("t_series_order_amount_statistics tsoas ON tso.id = tsoas.order_id");
            LEFT_OUTER_JOIN("( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_refund temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotr ON tsotr.order_id = tso.id");
            LEFT_OUTER_JOIN("( SELECT temp.order_id, temp.refund_status FROM t_series_order_tourist_quit temp ORDER BY temp.gmt_create DESC LIMIT 1 ) tsotq ON tsotq.order_id = tso.id");
            if(StringUtils.isNotBlank(req.getOrderNumber())) {
                WHERE("tso.order_number=#{req.orderNumber}");
            }
            if(StringUtils.isNotBlank(req.getReceiptNumber())) {
                WHERE("tso.id IN (SELECT tsor.order_id FROM t_series_order_receipt tsor WHERE " +
                        "tsor.receipt_number = #{req.receiptNumber})");
            }
            WHERE("tso.is_deleted=" + IsDeletedCode.NO.getCode());
            WHERE("tso.place_order_status=" + PlaceOrderStatusCode.CONFIRM.getCode());
            ORDER_BY("tso.gmt_create desc");
        }}.toString();
    }

    public String selectDetailBySelective(SeriesOrderDetailReq req) {
        return new SQL(){{
            SELECT("tso.id, tso.schedule_id, tso.order_number, " +
                    "tso.member_id, tso.member_name, tso.member_mobile, tso.member_email, tso.member_score, tso.member_level, tso.member_type, " +
                    "tso.contact_cn_surname, tso.contact_cn_name, tso.contact_en_surname, tso.contact_en_name,tso.big_client_id,tso.big_client_name, " +
                    "tso.contact_gender, tso.contact_telephones, tso.contact_email, tso.internal_remark, tso.customer_type, tso.is_deleted, "+
                    "(SELECT group_concat(CONCAT(temp_tsoti.main_data_id,'])', temp_tsoti.tourist_type_name,'])',temp_tsoti.people) SEPARATOR '})') " +
                    "FROM(SELECT tsoti.main_data_id, tsoti.tourist_type_name, count(*) AS people FROM t_series_order_tourist_info tsoti LEFT JOIN  t_series_order_tourist_quit t2 \n" +
                    "ON tsoti.id=t2.tourist_id " +
                    "WHERE tsoti.order_id = tso.id AND tsoti.is_deleted = 0 AND (t2.is_deleted = 0 or t2.is_deleted IS NULL ) AND (t2.refund_status  NOT IN(3) OR  t2.refund_status IS NULL) GROUP BY tsoti.main_data_id ) temp_tsoti) as touristPeopleList, " +
                    "tsoas.total_receivables, tsoas.total_paid_in,tsoas.remaining_receivables, tsoas.refund_amount, tsoas.total_tour_fee, tso.total_people, " +
                    "tsoas.collection_fee, tsoas.adjustment_cost, tsoas.other_amount "
                    + ", tso.agent_id, tso.agent_name, tso.agent_user_id, tso.agent_user_name");
            FROM("t_series_order tso");
            LEFT_OUTER_JOIN("t_series_order_amount_statistics tsoas ON tso.id = tsoas.order_id");
        //    WHERE("tso.place_order_status=" + PlaceOrderStatusCode.CONFIRM.getCode());
            WHERE("tso.id=#{id} AND tso.is_deleted = 0");
            OR();
            WHERE("tso.approval_id=#{id}");
        }}.toString();
    }
    
    /**
     * 查询所属系列团团期ID列表
     * @param req
     * @return
     */
    public String queryMyGroupId(SeriesOrderMyGroupIdQueryReq req) {
		return new SQL() {{
			SELECT("distinct t.schedule_id");
			FROM("t_series_order t");
			WHERE("t.create_id = #{userId}");
			if (req.getDepartDateBegin() != null) {
				WHERE("t.depart_date >= #{departDateBegin}");
			}
			if (req.getDepartDateEnd() != null) {
				WHERE("t.depart_date < #{departDateEnd}");
			}
		}}.toString();
	}
}
